


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS
** Definition of Start Point


		* clear
		clear
		clear 		mata
		mat 		drop _all

		* set path
		global 	path 	""					

		set maxvar 32767




/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 01 Import all .txt data files in input folder


{
** Create list of .txt files
local input : dir "$path/0 Data/BHCY9C/Input/2021" files "*.txt"

** Run loop
foreach file in `input' {
	* Import delimited data
	import delimited using "$path/0 Data/BHCY9C/Input/2021/`file'", delimit("^") varnames(1) clear stringcols(_all)
	
	* Save as single quarterly files
	compress
	save "$path/0 Data/BHCY9C/Output/2021/`file'.dta", replace
	}



// Combine single quartely files to panel 
clear
local bhc : dir "$path/0 Data/BHCY9C/Output/2021" files "*txt.dta"

** Append single files
foreach file in `bhc' {
	append using "$path/0 Data/BHCY9C/Output/2021/`file'"
	}
compress	
	
** Rename Ident variables	
rename 		rssd9001 entity
rename 		rssd9017 name
rename 		rssd9053 date_extinct
rename 		rssd9999 date_report
rename 		rssd9052 date_start	

** Remove empty rows	
drop 		if entity == "--------"	

order 		entity date* name

** Destring variables
destring 	entity bh*, replace

** Create date
gen year	= substr(date_report,1,4)
gen month	= substr(date_report,5,2)
gen day		= substr(date_report,7,2)

destring 	year month day, replace
gen 		date = mdy(month, day, year)
format 		date %td
drop 		year month day

order 		entity date* name
sort 		entity date

** Save
save "$path/0 Data/_Processed/0_BHC_Panel_Dataset_2023", replace
}


* Append "old" data with new file (2021 - Q2 2023)

use "$path/0 Data/_Processed/BHC_Panel_Dataset", clear
rename 		rssdid entity

append using "$path/0 Data/_Processed/0_BHC_Panel_Dataset_2023", force

save "$path/0 Data/_Processed/BHC_Panel_Dataset (v 2023)", replace


* Delete temp file

erase "$path/0 Data/_Processed/0_BHC_Panel_Dataset_2023.dta"


** Note: Last updated on 6 Oct 2023 with Q2 2023 data. 



/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 02: Import Matching Table for CRSP

{
** Import matching table
import delimited using "$path/0 Data/BHCY9C/Input/crsp_20220930V2.csv", clear

** Drop empty rows
drop if name == ""

** Adjust date variables
tostring dt_start dt_end, replace

foreach var of varlist dt_start dt_end {
	gen year	= substr(`var',1,4)
	gen month	= substr(`var',5,2)
	gen day		= substr(`var',7,2)

	destring year month day, replace
	gen match_`var' = mdy(month, day, year)
		format match_`var' %td
	drop year month day
	}
	
rename *_dt* **	
rename name name_match
drop notice dt_start dt_end

order entity match_start match_end name_match inst_type permco
sort entity match_start

** Save
compress
save "$path/0 Data/_Processed/BHC_CRSP_Matching_Table (v2023)", replace
}


** Updated Oct 6, 2023


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 03: Load Bank types from SNL

{
** Imort excel file
import excel using "$path/0 Data/SNL/SNL_Bank_Types.xls", firstrow cellrange(A3:I1624) clear

** Delete empty rows
drop if _n <= 3

** Prepare variables
destring 	BHC, gen(entity) force
destring	SIC, gen(sic)
drop 		if entity == .

** Create type dummies
gen bank_dummy 		= (Industry == "Bank")
gen thrift_dummy 	= (Industry == "Savings Bank/Thrift/Mutual")
gen non_bank_dummy	= (bank_dummy == 0 & thrift_dummy == 0)

** Assign SIC description
gen sic_des = ""
	replace sic_des = "Commercial Banks" 		if substr(SIC,1,3) == "602"
	replace sic_des = "Savings Institutions" 	if substr(SIC,1,3) == "603"
	replace sic_des = "Offices of BHCs"			if SIC			   == "6712"
	replace sic_des = "Other"					if sic_des		   == "" 	& sic != .


** Reduce to relevant variables
keep InstitutionName entity sic* Industry *_dummy
order entity Inst sic *_dummy

** Remove duplicates in terms of entity (only one, dropping makes no difference)
duplicates drop entity, force

compress
save "$path/0 Data/_Processed/SNL_Bank_Types", replace
}


** 6 Oct 2023: NOT updated

/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 04: Add Matching Table to BHC Data

{
** Load BHC Panel
use "$path/0 Data/_Processed/BHC_Panel_Dataset (v 2023)", clear

** Join by matching table (joinby commad as it is not a m:1 relation; duplicates in matching table)
joinby entity using "$path/0 Data/_Processed/BHC_CRSP_Matching_Table (v2023)"

** Check for duplicates as same entity is assigned more than once
duplicates tag entity date_report, gen(dup)
tab dup

* Manual Adjustment 
drop if dup > 0 & permco == 55670 & name == "BANCWEST CORPORATION"
drop if dup > 0 & permco ==  1718 & name == "FIRST HAWAIIAN, INC."
drop if dup > 0 & permco ==  51898 & name == "NATIONAL MERCANTILE BANCORP"
drop if dup > 0 & permco ==  9446 & name == "NATIONAL MERCANTILE BANCORP"
drop if dup > 0 & permco ==  4183 & name == "KEYCORP"
drop if dup > 0 & permco ==  9446 & name == "FIRST CALIFORNIA FINANCIAL GROUP, INC."
drop if dup > 0 & permco ==  9446 & name == "FIRST CALIFORNIA FINANCIAL GROUP"
drop if dup > 0 & permco ==  51989 & name == "FIRST CALIFORNIA FINANCIAL GROUP"
drop if dup > 0 & permco ==  7067 & name == "OLD NATIONAL BANCORP"



drop dup
duplicates tag entity date_report, gen(dup)
tab dup

* Drop if report lies before start of matching
drop if date < match_start & dup > 0 

* Drop if report lies after match end (except "natural" end as of 12/31/2018)
drop if date > match_end & match_end != mdy(09,30,2022)  & dup > 0 
drop dup

* Remove permco if not within range of matching period (but save as string)
tostring permco, gen(permco_str)  
replace permco = . if (date < match_start | date > match_end & match_end != mdy(09,30,2022))

** Drop variables without any observation
dropmiss *, force

** Add entity types (probably only traded were downloaded form SNL)
merge m:1 entity using "$path/0 Data/_Processed/SNL_Bank_Types"
drop if _merge == 2
drop _merge

** Save
compress
save "$path/0 Data/_Processed/BHC_Panel_Dataset_CRSP_Matched (v 2023)", replace
}


** Note: last updated on 6 October 2023


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 05: Create Variables for analysis

** Load Dataset
use "$path/0 Data/_Processed/BHC_Panel_Dataset_CRSP_Matched (v 2023)",clear

** Create quarter date variables
gen month = month(date)
gen year  = year(date)
gen yq 	  = qofd(date)
	format yq %tq

	
*Total loans (differentiation between hfs and hfi only since 2001)
rename bhck5369 assets_loans_hfs
rename bhckb528 assets_loans_hfi
rename bhck3123 assets_loans_allowance
rename bhckb529 assets_loans_hfi_net

egen assets_loans_total 		= rowtotal(assets_loans_hfs assets_loans_hfi) if year > 2000, missing
	replace assets_loans_total 	= bhck2122 if assets_loans_total == . & year == 2000
egen assets_loans_net_total 	= rowtotal(assets_loans_hfs assets_loans_hfi_net)  if year > 2000, missing
	replace assets_loans_net_total = bhck2125 if assets_loans_net_total == . & year == 2000


* Total Assets
rename bhck2170 assets_total

* Total Equity
rename bhck3210 equity_capital_total

* Noninterest and net income
rename bhck4079 is_nonint_income_total
rename bhck4340 is_net_income_total


* Total deposits
rename bhdm6631 liab_deposits_domestic_non_ib
rename bhdm6636 liab_deposits_domestic_ib
rename bhfn6631 liab_deposits_foreign_non_ib
rename bhfn6636 liab_deposits_foreign_ib

egen liab_deposits_total = rowtotal(liab_deposits_domestic_non_ib liab_deposits_domestic_ib liab_deposits_foreign_non_ib liab_deposits_foreign_ib), missing


* Total time deposits
rename bhcbhk29 liab_time_deposits_below_250
rename bhcb6648 liab_time_deposits_below_100
rename bhod6648 liab_time_deposits_below_100_o
rename bhodhk29 liab_time_deposits_below_250_o

* Before 2017Q1 (probably due to deposit insurace: >$100k)
rename bhcb2604 liab_time_deposits_100
rename bhod2604 liab_time_deposits_100_other
* From 2017Q1 (probably due to deposit insurace: >$250k)
rename bhcbj474 liab_time_deposits_250
rename bhodj474 liab_time_deposits_250_other

egen liab_time_deposits_large = rowtotal(liab_time_deposits_100 liab_time_deposits_100_other liab_time_deposits_250 liab_time_deposits_250_other), missing

egen liab_time_deposits_total = rowtotal(liab_time_deposits_below_100 liab_time_deposits_100 liab_time_deposits_below_100_o liab_time_deposits_100_other liab_time_deposits_below_250 liab_time_deposits_250 liab_time_deposits_below_250_o liab_time_deposits_250_other), missing



* Consumer loans (variable change in 2010; i.e. bhck2011 is substituted)
egen assets_loans_consumer    = rowtotal(bhckb538 bhckb539 bhck2011 bhckk137 bhckk207), missing

* Unnused credit card debt (variable change in 2010) // Since 2018Q2 only to be reported semi-annually (June and December)
egen offbs_unused_cc	 = rowtotal(bhck3815 bhckj455 bhckj456), missing

* C&I loans
egen assets_loans_ci = rowtotal(bhck1763 bhck1764 bhckkx56), missing

* RWA (variable change in 2015)
gen reg_rwa_total = bhcka223
	replace reg_rwa_total = bhcaa223 if reg_rwa_total == .
	
* Tier 1 Capital (variable change in 2014)
gen reg_capital_tier1 = bhck8274
	replace reg_capital_tier1 = bhca8274 if reg_capital_tier1 == .
	
* Tier 1 Ratio (variable change in 2014)
gen reg_tier1_ratio = bhck7206
	replace reg_tier1_ratio = bhca7206 if reg_tier1_ratio == .

* Total Capital (variable change in 2014)
gen reg_capital_total = bhck3792
	replace reg_capital_total = bhca3792 if reg_capital_total == .
	
* Total Capital Ratio (variable change in 2014)
gen reg_capital_ratio = bhck7205
	replace reg_capital_ratio = bhca7205 if reg_capital_ratio == .

* Leverage Ratio (tier-1/assets) (variable change in 2014)
gen reg_leverage_ratio = bhck7204
	replace reg_leverage_ratio = bhca7204 if reg_leverage_ratio == .
	
* NPL (change in 2018Q1 from 5525/6 to 1403/7)
egen assets_loans_npl = rowtotal(bhck5525 bhck5526 bhck1407 bhck1403), missing




****************************************************************************
************* A) UNUSED COMMITMENTS ****************************************

** Variables for "unused commitments"
rename bhck3814 offbs_family_creditlines
rename bhck3816 offbs_real_estate_creditlines
rename bhck6550 offbs_real_estate_unsecured
rename bhck3817 offbs_underwriting

* Before 2010Q1
rename bhck3818 offbs_unused_commitments_other
* From 2010Q1 
rename bhckj457 offbs_unused_loans
rename bhckj458 offbs_unused_fi
rename bhckj459 offbs_unused_other

rename bhck3411 offbs_letter_of_credit

* Time consistent variable (is zero if before zero components of unused credit lines is zero)
egen unused_commitments = rowtotal(offbs_family_creditlines offbs_real_estate_creditlines offbs_real_estate_unsecured offbs_underwriting offbs_unused_loans offbs_unused_fi offbs_unused_other offbs_letter_of_credit offbs_unused_commitments_other), missing




****************************************************************************
************* B) WHOLSALE FUNDING ******************************************

** Variables for "wholsale funding"
rename bhck4062 liab_subordinated_debt

* Before 2002Q1
rename bhck2800 liab_fed_funds_pre_2002
* From 2002Q1
rename bhdmb993 liab_fed_funds_domestic
rename bhckb995 liab_fed_funds_repurchase

egen 	liab_fed_funds = rowtotal(liab_fed_funds_pre_2002 liab_fed_funds_domestic liab_fed_funds_repurchase)

gen 	repo_activity = liab_fed_funds / assets_total

* Before2002Q1
rename bhck2332 liab_other_borrowed_money_st
rename bhck2333 liab_other_borrowed_money_lt
* From 2002Q1 (Note: not exactly similar as previous variable still exist)
rename bhck3190 liab_other_borrowed_money

gen 	liab_borrowed_money = liab_other_borrowed_money_st + liab_other_borrowed_money_lt if year(date) <= 2001
replace liab_borrowed_money = liab_other_borrowed_money if year(date) >= 2002


** Version 1: with large time deposits (note: limit changed from $100k to $250k in 2017)
egen wholesale_funding = rowtotal(liab_time_deposits_large liab_deposits_foreign_non_ib liab_deposits_foreign_ib liab_subordinated_debt liab_fed_funds liab_borrowed_money), missing

** Version 2: with all time deposits 
egen wholesale_funding_v2 = rowtotal(liab_time_deposits_total liab_deposits_foreign_non_ib liab_deposits_foreign_ib liab_subordinated_debt liab_fed_funds liab_borrowed_money), missing

** Version 3: without large time deposits
egen wholesale_funding_v3 = rowtotal(liab_deposits_foreign_non_ib liab_deposits_foreign_ib liab_subordinated_debt liab_fed_funds liab_borrowed_money), missing



****************************************************************************
************* C) LIQUIDITY *************************************************

** Cash&Balances
rename bhck0081 assets_non_ib_balances
rename bhck0395 assets_ib_us_balances
rename bhck0397 assets_ib_non_us_balances

egen assets_cash_and_balances = rowtotal(assets_non_ib_balances assets_ib_us_balances assets_ib_non_us_balances), missing

** Fed Funds
* Before 2002Q1
rename bhck1350 assets_fed_funds_pre02
* From 2002Q1
rename bhdmb987 assets_fed_funds_domestic
rename bhckb989 assets_fed_funds_resell

egen 	assets_fed_funds = rowtotal(assets_fed_funds_pre02 assets_fed_funds_domestic assets_fed_funds_resell)

** Total securities 
rename bhck1754 assets_securities_htm
rename bhck1773 assets_securities_afs
rename bhckja22 assets_securities_equity

egen assets_securities_total = rowtotal(assets_securities_htm assets_securities_afs assets_securities_equity), missing


** Held-to-Maturity mortgage pass-through securities (amortized cost)
*  Before 2009Q2
egen assets_securities_htm_mbs_pre09	= rowtotal(bhck1698 bhck1703 bhck1709 bhck1714 bhck1718 bhck1733), missing
*  From 2009Q2
egen assets_securities_htm_mbs_09 		= rowtotal(bhckg300 bhckg304 bhckg308 bhckkx52 bhckg312 bhckg316 bhckg320), missing
*  Combined
egen assets_securities_htm_mbs 	= rowtotal(assets_securities_htm_mbs_pre09 assets_securities_htm_mbs_09), missing


** Available-for-Sale mortgage pass-through securities (fair value)
*  Before 2009Q2
egen assets_securities_afs_mbs_pre09 	= rowtotal(bhck1702 bhck1707 bhck1713 bhck1717 bhck1732 bhck1736), missing
*  From 2009Q2
egen assets_securities_afs_mbs_09 		= rowtotal(bhckg303 bhckg307 bhckg311 bhckkx55 bhckg315 bhckg319 bhckg323), missing
*  Combined 
egen assets_securities_afs_mbs			= rowtotal(assets_securities_afs_mbs_pre09 assets_securities_afs_mbs_09), missing 

 
** ABS
egen assets_securities_htm_abs_pre06 = rowtotal(bhckb838 bhckb842 bhckb846 bhckb850 bhckb854 bhckb858) if year <= 2005, missing
gen assets_securities_htm_abs_post06 = bhckc026 if year >= 2006

egen assets_securities_afs_abs_pre06 = rowtotal(bhckb841 bhckb845 bhckb849 bhckb853 bhckb857 bhckb861) if year <= 2005, missing
gen assets_securities_afs_abs_post06 = bhckc027 if year >= 2006



egen assets_securities_mbs_abs = rowtotal(assets_securities_htm_mbs assets_securities_afs_mbs assets_securities_htm_abs_pre06 assets_securities_htm_abs_pre06 assets_securities_afs_abs_pre06 assets_securities_afs_abs_pre06), missing

egen assets_liquid_incl_mbs_abs = rowtotal(assets_cash_and_balances assets_fed_funds assets_securities_htm assets_securities_afs), missing

gen liquidity = assets_liquid_incl_mbs_abs - assets_securities_mbs_abs
	


****************************************************************************
************* D) LIQUIDITY RISK ********************************************

** V1: wholesale includes only large time deposits (jumps down due to shift in limit)
gen liquidity_risk 		= (unused_commitments + wholesale_funding - liquidity) / assets_total

** V2: wholesale includes all time deposits
gen liquidity_risk_v2 	= (unused_commitments + wholesale_funding_v2- liquidity) / assets_total

** V3: without large time deposits
gen liquidity_risk_v3 	= (unused_commitments + wholesale_funding_v3 - liquidity) / assets_total



****************************************************************************
************* E) OTHER MEASURES ********************************************

* Unused commitments
******************************
gen unused_com_loans 		= unused_commitments + assets_loans_hfi
gen unused_com_ratio 		= unused_commitments / unused_com_loans
gen unused_com_assets 		= unused_commitments / assets_total

* Liquidity ratios
******************************
gen liquidity_assets		= liquidity / assets_total

* Wholsale funding
****************************
gen wholesale_assets		= wholesale_funding 	/ assets_total
gen wholesale_assets_v2		= wholesale_funding_v2 	/ assets_total
gen wholesale_assets_v3		= wholesale_funding_v3 	/ assets_total

* Net wholesale 
*****************************
gen net_wholesale_assets	= (wholesale_funding 	- liquidity) / assets_total
gen net_wholesale_assets_v2	= (wholesale_funding_v2 - liquidity) / assets_total
gen net_wholesale_assets_v3	= (wholesale_funding_v3 - liquidity) / assets_total

* Non-performing loans
*****************************
gen npl_loans				= assets_loans_npl / assets_loans_hfi

* RWA to Assets ratio
*****************************
gen rwa_ta					= reg_rwa_total / assets_total

* Capital Ratios
****************************
gen capital					= equity_capital_total 	/ assets_total					
gen capital_reg_assets		= reg_capital_total 	/ assets_total
gen capital_reg_rwa			= reg_capital_total 	/ reg_rwa_total

* Other liquidity ratios
****************************
gen	unused_wholesale		= (unused_commitments + wholesale_funding) / liquidity
gen	unused_com_liquidity	= (unused_commitments) / liquidity
gen	wholesale_liquidity		= (wholesale_funding) / liquidity

* Used credit card debt
****************************
gen unused_cc				= offbs_unused_cc / assets_total

 
* Consumer loan portfolio (total consumer loans + off b/s cc exposure)
***************************
gen consumer 				= (assets_loans_consumer + offbs_unused_cc) / assets_total

* C&I portfolio
***************************
gen comm_ind				= assets_loans_ci / assets_total

* Deposit Ratio
***************************
gen deposit_assets			= liab_deposits_total / assets_total




****************************************************************************
************* F) CONTROL VARIABLES *****************************************


* Share of noninterest income from operating revenue (SNL definition)
***************************
* Operating revenue: net interest income (before provisions) + noninterest income
egen is_operating_revenue 	= rowtotal(bhck4074 is_nonint_income_total)
gen nonintinc 			 	= is_nonint_income_total / is_operating_revenue

* Note: IS items are quarterly items which cumulate over time. Given that all items //
// 		here are from the IS, this should not be an issue

* Log of total assets
***************************
gen log_assets	= log(assets_total)


* Rolling 4-quarter ROA
***************************	
*  i) Create a full panel so that the correct four quarters are added
gen original_observation = 1
xtset entity yq
tsfill
replace original_observation = 0 if original_observation == .
sort entity yq

* ii) compute quarterly net income as figure in Report is CUMULATIVE!!
gen net_income_quarterly 			= is_net_income_total 						   if month == 3
	replace net_income_quarterly 	= is_net_income_total - l1.is_net_income_total if month != 3 & year == l1.year
	
* iii) calculate 4-quarter rolling net income (should equal is_net_income_total in Q4)
gen net_income_annualized = net_income_quarterly + l1.net_income_quarterly + l2.net_income_quarterly + l3.net_income_quarterly if year - l3.year <= 1
* Adjust special cases where year-end NI is available but not all quarters
replace net_income_annualized = is_net_income_total    if month == 12 & net_income_annualized == .

* iv) Remove inserted observations again
drop if original_observation == 0
drop original_observation

gen roa_quarterly 	= net_income_quarterly / assets_total
gen roa				= net_income_annualized / assets_total


	
* Average of ROA + CAR (CAR is in %)
***************************		
gen	mean_roa_car	=	(roa+reg_capital_ratio/100)/2	
	
	
* Total Deposits to Assets Ratio
***************************		
gen deposits_assets	= liab_deposits_total / assets_total


* Total Deposits to Loans Ratio
***************************	
gen deposits_loans	= liab_deposits_total / assets_loans_total



* Derivatives Book (Interest Rate + Exchange Rate + Credit)
*****************************
egen derivatives_interest		= rowtotal(bhcka126 bhck8725), missing
egen derivatives_exchange		= rowtotal(bhcka127 bhck8726), missing 
egen derivatives_credit_pre06	= rowtotal(bhcka534 bhcka535), missing
egen derivatives_credit_post06	= rowtotal(bhckc969 bhckc971 bhckc973 bhckc975 bhckc968 bhckc970 bhckc972 bhckc974), missing
egen derivatives_credit			= rowtotal(derivatives_credit_pre06 derivatives_credit_post06), missing

egen derivatives_financial_total= rowtotal(derivatives_interest derivatives_exchange derivatives_credit), missing
gen  derivatives_to_assets		= derivatives_financial_total / assets_total




* Beltrati/Stulz (2012, JFE) Controls
******************************************

* Tier-1 Ratio already created above
* Real Estate Beta estiamted in seprate file
* Idiosyncratic Volatility (SE of Residuals) also in other File

* income diversity from Laeven and Levine (2009) is defined as one minus the absolute value of the ratio of the difference between net interest income and other operating income to total operating income
gen is_net_interest_income = bhck4074
gen income_diversity = 1 - abs((is_net_interest_income - is_nonint_income_total)/is_operating_revenue)


* non-interest is the share of operating income not due to interest income
gen non_interest = is_nonint_income_total/is_operating_revenue

* log Z is the distance to default estimated as Z = mean(ROA+CAR)/volatility(ROA) where CAR is the capital-to-asset ratio and ROA is return on assets for the period 1996–2006
xtset entity yq
forvalues k = 1/12 {
	by entity: gen roa_l`k' = l`k'.roa
	}
egen roa_sd = rowsd(roa_l*)
drop roa_l*
	
gen log_Z = log( ((roa+capital)/2)/roa_sd )	


* funding fragility is the ratio between the sum of deposits from other banks, other deposits, and short-term borrowing over total deposits plus money market and short-term funding
* --> items are not available on report (i.e. deposits from other banks) 

*Other earning assets is the ratio between the sum of derivatives, other securities, and other remaining assets and the sum of loans and other earning assets.

gen other_earning_assets = bhckb985
gen assets_trading		 = bhck3401

gen other_earning_assets_ratio = derivatives_financial_total / (other_earning_assets + assets_trading + assets_loans_net_total)

*tangible equity (equity minus intangible assets whenever available or equity when intangible assets are not available divided by total assets).

egen 	intangibles 		= rowtotal(bhck3164 bhck3163 bhckjf76), missing
gen 	assets_intangible 	= bhck2143
replace assets_intangible = intangibles if assets_intangible == .
	
gen 	tangible_equity 	= (equity_capital_total - assets_intangible) / assets_total
replace tangible_equity 	= equity_capital_total / assets_total if tangible_equity == .

*loans to assets
gen loans_assets = assets_loans_net_total / assets_total




* Primary Dealer (taken from https://www.newyorkfed.org/markets/primarydealers#primary-dealers ; see excel file "Dealer_Lists_1960_to_2014" in data folder)
* Note: currently only current (as of 2019) primary dealers are identified (hand matching of names required)
*********************************
gen 	current_primary_dealer		=	1  		if	inlist(entity,1575569, 5006575, 1073757, 1951350, 1574834, 1032473, 1020201, 2380443, 1039502, 5034792, 2162966, 5280254, 1249196, 4846998, 1120754)
replace current_primary_dealer 		= 0 if current_primary_dealer == .





** Create type identifier
gen report_type = ""
	replace report_type = "FR Y-9SP" 	if (bhsp2170 != .)
	replace report_type = "FR Y-9LP" 	if (bhcp2170 != .)
	replace report_type = "FR Y-9C" 	if (assets_total != .)
	


** Reduce Dataset
*keep entity permco permco_str date yq name liquidity_risk unused_com_ratio unused_com_assets liquidity_assets-deposit_assets assets_total report_type

order entity permco permco_str date yq year month name liquidity_risk unused_com_ratio unused_com_assets liquidity_assets-deposit_assets assets_total net_income_quarterly net_income_annualized report_type


** Save
compress 
save "$path/0 Data/_Processed/BHC_Bank_Ratios (v 2023)", replace






* Note: Last update 6 October 2023



